/**
 * 笔记数据模型
 * 处理笔记相关的数据库操作
 */
const { query } = require('../config/database');

const noteModel = {
  /**
   * 获取用户的所有笔记
   * @param {number} userId - 用户ID
   * @param {Object} filters - 过滤条件
   * @returns {Promise<Array>} 笔记列表
   */
  findAll: async (userId, filters = {}) => {
    // 基础查询
    let sql = `
      SELECT id, title, SUBSTRING(content, 1, 100) as preview, tags, created_at, updated_at
      FROM notes
      WHERE user_id = ?
    `;
    
    const queryParams = [userId];
    
    // 搜索过滤
    if (filters.search) {
      sql += ' AND (title LIKE ? OR content LIKE ? OR tags LIKE ?)';
      const searchTerm = `%${filters.search}%`;
      queryParams.push(searchTerm, searchTerm, searchTerm);
    }
    
    // 排序
    const allowedSortFields = ['title', 'created_at', 'updated_at'];
    const sort = allowedSortFields.includes(filters.sort) ? filters.sort : 'updated_at';
    const order = filters.order?.toUpperCase() === 'ASC' ? 'ASC' : 'DESC';
    
    sql += ` ORDER BY ${sort} ${order}`;
    
    // 分页
    if (filters.limit) {
      sql += ' LIMIT ?';
      queryParams.push(parseInt(filters.limit));
      
      if (filters.offset) {
        sql += ' OFFSET ?';
        queryParams.push(parseInt(filters.offset));
      }
    }
    
    return await query(sql, queryParams);
  },

  /**
   * 根据ID获取笔记
   * @param {number} id - 笔记ID
   * @param {number} userId - 用户ID
   * @returns {Promise<Object|null>} 笔记对象或null
   */
  findById: async (id, userId) => {
    const sql = 'SELECT * FROM notes WHERE id = ? AND user_id = ?';
    const notes = await query(sql, [id, userId]);
    return notes.length ? notes[0] : null;
  },

  /**
   * 创建新笔记
   * @param {Object} noteData - 笔记数据
   * @returns {Promise<Object>} 创建的笔记
   */
  create: async (noteData) => {
    const sql = 'INSERT INTO notes (title, content, tags, user_id) VALUES (?, ?, ?, ?)';
    
    const result = await query(sql, [
      noteData.title || '未命名笔记',
      noteData.content || '',
      noteData.tags || null,
      noteData.user_id
    ]);
    
    // 获取完整的笔记数据
    const newNoteId = result.insertId;
    const newNote = await noteModel.findById(newNoteId, noteData.user_id);
    
    return newNote;
  },

  /**
   * 更新笔记
   * @param {number} id - 笔记ID
   * @param {number} userId - 用户ID
   * @param {Object} updateData - 要更新的字段
   * @returns {Promise<boolean>} 更新是否成功
   */
  update: async (id, userId, updateData) => {
    const allowedFields = ['title', 'content', 'tags'];
    const fields = [];
    const values = [];

    // 构建动态更新SQL
    for (const field of allowedFields) {
      if (updateData[field] !== undefined) {
        fields.push(`${field} = ?`);
        values.push(updateData[field]);
      }
    }

    if (fields.length === 0) return false;

    // 添加更新时间
    fields.push('updated_at = CURRENT_TIMESTAMP()');

    values.push(id, userId);
    const sql = `UPDATE notes SET ${fields.join(', ')} WHERE id = ? AND user_id = ?`;
    const result = await query(sql, values);
    
    return result.affectedRows > 0;
  },

  /**
   * 删除笔记
   * @param {number} id - 笔记ID
   * @param {number} userId - 用户ID
   * @returns {Promise<boolean>} 删除是否成功
   */
  delete: async (id, userId) => {
    const sql = 'DELETE FROM notes WHERE id = ? AND user_id = ?';
    const result = await query(sql, [id, userId]);
    return result.affectedRows > 0;
  },

  /**
   * 获取热门标签列表
   * @param {number} userId - 用户ID
   * @param {number} limit - 返回的标签数量
   * @returns {Promise<Array>} 标签列表
   */
  getPopularTags: async (userId, limit = 10) => {
    // 获取所有笔记的标签
    const notesWithTags = await query(
      'SELECT tags FROM notes WHERE user_id = ? AND tags IS NOT NULL',
      [userId]
    );
    
    // 统计标签
    const tagCounts = {};
    
    notesWithTags.forEach(note => {
      if (!note.tags) return;
      
      const tags = note.tags.split(',').map(tag => tag.trim());
      
      tags.forEach(tag => {
        if (tag) {
          tagCounts[tag] = (tagCounts[tag] || 0) + 1;
        }
      });
    });
    
    // 转换为数组并排序
    const sortedTags = Object.entries(tagCounts)
      .map(([tag, count]) => ({ tag, count }))
      .sort((a, b) => b.count - a.count)
      .slice(0, limit);
    
    return sortedTags;
  }
};

module.exports = noteModel; 